package com.ls.dbpool;

import java.io.File;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.UUID;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

public class DBUtility {
	private static Logger logger = Logger.getLogger(DBUtility.class);
	private static BasicDataSource dataSource = null;
	private static QueryRunner qRunner;
	static {
		init();
	}

	public static void init() {
		Properties dbProps = new Properties();
		try {
			dbProps.load(getDbConfigInput());
			String driveClassName = dbProps.getProperty("jdbc.driverClassName", "org.postgresql.Driver");
			String url = dbProps.getProperty("jdbc.url");
			String username = dbProps.getProperty("jdbc.username");
			String password = dbProps.getProperty("jdbc.password");
			String initialSize = dbProps.getProperty("dataSource.initialSize", "10");
			String minIdle = dbProps.getProperty("dataSource.minIdle", "5");
			String maxIdle = dbProps.getProperty("dataSource.maxIdle", "20");
			String maxWait = dbProps.getProperty("dataSource.maxWait", "1000");
			String maxActive = dbProps.getProperty("dataSource.maxActive", "50");

			dataSource = new BasicDataSource();
			dataSource.setDriverClassName(driveClassName);
			dataSource.setUrl(url);
			dataSource.setUsername(username);
			dataSource.setPassword(password);

			// 初始化连接数
			if (initialSize != null)
				dataSource.setInitialSize(Integer.parseInt(initialSize));

			// 最小空闲连接
			if (minIdle != null)
				dataSource.setMinIdle(Integer.parseInt(minIdle));

			// 最大空闲连接
			if (maxIdle != null)
				dataSource.setMaxIdle(Integer.parseInt(maxIdle));

			// 超时回收时间(以毫秒为单位)
			if (maxWait != null)
				dataSource.setMaxWaitMillis(Long.parseLong(maxWait));

			// 最大连接数
			if (maxActive != null) {
				if (!maxActive.trim().equals("0"))
					dataSource.setMaxTotal(Integer.parseInt(maxActive));
			}
			qRunner = new QueryRunner(dataSource);
		} catch (Exception e) {
			logger.error("创建连接池失败!请检查设置!!!");
		}
	}

	private static InputStream getDbConfigInput() {
		return DBUtility.class.getClassLoader().getResourceAsStream("db.properties");
	}

	/**
	 * TODO 获取db信息
	 * 
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	private static Properties getDbProps(String xmlFile) {
		Properties p = new Properties();
		try {
			SAXReader reader = new SAXReader();
			File f = new File(xmlFile);
			Document document = reader.read(f);
			Element element = document.getRootElement();
			Element ele = element.element("dataSource");
			for (Iterator iter = ele.elementIterator(); iter.hasNext();) {
				Element e = (Element) iter.next();
				p.put(e.getName(), String.valueOf(e.getData()));
			}
		} catch (Exception e) {
			logger.error("failed read xml file " + xmlFile, e);
		}
		return p;
	}

	public static int execUpdate(String sql) throws SQLException {
		int rs = qRunner.update(sql);
		return rs;
	}
	
	public static int execUpdate(String sql,Connection conn) throws SQLException {
		int rs = qRunner.update(sql,conn);
		return rs;
	}

	public static int[] exexBatch(String sql, Object[][] params) throws SQLException {
		return qRunner.batch(sql, params);
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static <T> List<T> execSelect(String sql, Class<T> type) throws SQLException {
		List<T> list = (List<T>) qRunner.query(sql, new BeanListHandler(type));
		return list;
	}

	public static List<Object[]> execSelect(String sql) throws SQLException {
		List<Object[]> list = qRunner.query(sql, new ArrayListHandler());
		return list;
	}

	/**
	 * 返回一个值的select语句，如select count(*)
	 * 
	 * @param sql
	 * @param type
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	public static <T> T execBasicSelect(String sql, Class<T> type) throws SQLException {
		List<Object[]> list = qRunner.query(sql, new ArrayListHandler());
		if (list == null || list.isEmpty() || list.get(0).length == 0)
			return null;
		return (T) list.get(0)[0];
	}
	
	public static <T> T execBasicSelect(String sql, Class<T> type,Connection conn) throws SQLException {
		List<Object[]> list = qRunner.query(sql, new ArrayListHandler(),conn);
		if (list == null || list.isEmpty() || list.get(0).length == 0)
			return null;
		return (T) list.get(0)[0];
	}

	public static void main(String[] args) throws SQLException {
/*		Map<String, List<List<String>>> map = DiskCleanManager.getIntance().getLogTablesNames();
		DiskCleanManager.getIntance().cleanLogs(map,false);
		List<List<String>> list = map.get("day");
		for(List<String> ll :list){
			System.out.println(ll);
		}
		if(true)
			return;*/
		String sql1 = "CREATE TABLE %s (code CHARACTER(36) NOT NULL, createtime TIMESTAMP(6) WITHOUT TIME ZONE, hostname CHARACTER VARYING(100), type CHARACTER VARYING(100), serverip CHARACTER VARYING(128), instance CHARACTER VARYING(36), path CHARACTER VARYING(200), appcode CHARACTER VARYING(36), mwinstancename CHARACTER VARYING(100), callid CHARACTER VARYING(100), thread CHARACTER VARYING(200), userid CHARACTER VARYING(100), ts CHARACTER VARYING(19), sql TEXT, costtime BIGINT, rownum BIGINT, PRIMARY KEY (code));";
		String insert = "INSERT INTO %s (code, createtime, hostname, type, serverip, instance, path, appcode, mwinstancename, callid, thread, userid, ts, sql, costtime, rownum) VALUES ('%s', '2016-07-06 19:00:02', 'somaagent2', 'APP_NC_MSQL-LOG', '20.12.16.91', '8470f125_8c78_41bb_8aa5_dd7aa613c648', '', '7770202d_0bbd_4ac7_9809_26ad32436c83', 'nc63_mwserver1', '', '[Thread-41]', '', '2016-07-06 19:01:02', 'select * from ( select row_.*, rownum rownum_ from ( select pk_sysinit, ts from cp_sysinit order by ts desc ) row_ where rownum <= 2 ) where rownum_ > 0', 0, 2);";
		String tn = "cc_log_app_nc_msql_%02d";
		
		String tn1;
		String sql11;
		String insert11;
		for (int k = 0; k < 1; k++) {
			int count = 1000;
			for (int i = 0; i < 24; i++) {
				tn1 = String.format(tn, i);
				sql11 = String.format(sql1, tn1);
				/*try{
				 DBUtility.execUpdate(sql11);
				}catch(Exception e){
					System.out.println(e);
				}*/
				StringBuilder sb = new StringBuilder();
				for (int j = 0; j < count; j++) {
					insert11 = String.format(insert, tn1, UUID.randomUUID().toString());
					sb.append(insert11).append(";");
				}
				DBUtility.execUpdate(sb.toString());
			}
		}
	}
}